/**
 * 
 */
package com.feiyu.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * 
 * @date 2017年7月21日下午2:48:25
 *
 * @author nanshouxiao
 * 
 */
public class ExcelUtils {

	/**
	 * excle2003之前的类型
	 */
	private static final String EXCEL_2003L = "xls";

	/**
	 * excle2007及之后的类型
	 */
	private static final String EXCEL_2007U = "xlsx";

	/**
	 * 默认的格式
	 */
	private static SimpleDateFormat simpleDataFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

	/**
	 * 
	 * 根据文件File获取实体类的list
	 * 
	 * @param file
	 * @param entityClass
	 * @param titles
	 * @return
	 */
	public static <E> List<E> getEntityList(File file, Class<E> entityClass, String[] titles) {
		InputStream input = null;
		try {
			input = new FileInputStream(file);
			List<E> list = getEntityList(input, file.getName(), entityClass, titles);
			return list;
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			closeInputStream(input);
		}
		return null;
	}

	/**
	 * 关闭输入流，可以调用CloseUtil里的close方法
	 * 
	 * @param input
	 */
	private static void closeInputStream(InputStream input) {
		if (input != null) {
			try {
				input.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 根据输入流以及文件名称获取实体类List
	 * 
	 * @param input
	 * @param fileName
	 * @param entityClass
	 * @param titles
	 * @return
	 */
	public static <E> List<E> getEntityList(InputStream input, String fileName, Class<E> entityClass, String[] titles) {
		Workbook hsswork = getWorkbook(input, fileName);
		Sheet sheet = hsswork.getSheetAt(0);
		List<E> list = getEntityList(sheet, entityClass, titles);
		return list;
	}

	/**
	 * 从sheet页里获取数据，然后转换成对应的list<Bean>
	 * 
	 * @param sheet
	 * @param clazz
	 * @param titles
	 * @return
	 */
	private static <E> List<E> getEntityList(Sheet sheet, Class<E> clazz, String[] titles) {
		List<E> tlist = null;
		int titleLength = titles.length;
		int columnlength = sheet.getLastRowNum();
		if (columnlength > 0) {
			tlist = new ArrayList<E>();
			Map<String, Object> entityMap = null;
			for (int i = 1; i <= columnlength; i++) {
				entityMap = new HashMap<>();
				Row row = sheet.getRow(i);
				if(row == null){
					continue;
				}
				int rowlength = row.getLastCellNum();
				int minLength = Math.min(rowlength, titleLength);
				for (int j = 0; j < minLength; j++) {
					Cell cell = row.getCell(j);
					String key = titles[j];
					if (cell != null) {
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							entityMap.put(key, cell.getStringCellValue());
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								entityMap.put(key, cell.getDateCellValue());
							} else {
								entityMap.put(key, cell.getNumericCellValue());
							}
							;
							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							entityMap.put(key, cell.getCellFormula());
							break;
						default:
							entityMap.put(key, cell.getStringCellValue());
							break;
						}
					}
				}

				tlist.add(convertMapToEntity(entityMap, clazz));
			}
		} else {
			return null;
		}
		return tlist;

	}

	/**
	 * 根据文件名后缀获取不同的workbook
	 * 
	 * @param input
	 * @param fileName
	 * @return
	 */
	private static Workbook getWorkbook(InputStream input, String fileName) {
		Workbook wb = null;
		try {
			if (fileName == null) {

			} else if (fileName.toLowerCase().endsWith(EXCEL_2003L)) {
				wb = new HSSFWorkbook(input); // 2003-
			} else if (fileName.toLowerCase().endsWith(EXCEL_2007U)) {
				wb = new XSSFWorkbook(input); // 2007+
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return wb;

	}

	/**
	 * 将map转换为实体
	 * 
	 * @param map
	 * @param clazz
	 * @return
	 */
	private static <E> E convertMapToEntity(Map<String, Object> map, Class<E> clazz) {
		try {
			if(Map.class.isAssignableFrom(clazz)){
				return (E) map;
			}
			E entity = clazz.newInstance();
			Field[] fields = clazz.getDeclaredFields();
			Map<String, Field> fieldNameToField = new HashMap<>();
			for (Field field : fields) {
				fieldNameToField.put(field.getName(), field);
			}
			for (Entry<String, Object> entry : map.entrySet()) {
				String key = entry.getKey();
				Object value = entry.getValue();
				String strValue = (value == null ? null : String.valueOf(value));
				Field field = fieldNameToField.get(key);
				Object trueValue = null;
				if (field != null) {
					Class<?> fieldType = field.getType();
					field.setAccessible(true);
					if (fieldType == String.class) {
						if (value instanceof Date) {
							trueValue = simpleDataFormat.format(value);
						} else {
							trueValue = strValue;
						}
					} else if (fieldType == Short.class) {
						trueValue = Short.valueOf(strValue);
					} else if (fieldType == Integer.class) {
						trueValue = Integer.valueOf(strValue);
					} else if (fieldType == Long.class) {
						trueValue = Long.valueOf(strValue);
					} else if (fieldType == Float.class) {
						trueValue = Float.valueOf(strValue);
					} else if (fieldType == Double.class) {
						trueValue = Double.valueOf(strValue);
					} else if (fieldType == Date.class) {
						if (value instanceof Date) {
							trueValue = value;
						} else {
							try {
								trueValue = simpleDataFormat.parse(strValue);
							} catch (ParseException e) {
								e.printStackTrace();
							}
						}
					}
					field.set(entity, trueValue);
				}
			}
			return entity;
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 写入excel
	 * 
	 * @param titleList
	 * @param dataList
	 * @param outputStream
	 * @param dataProperties
	 */
	public static void exportDataToExcel(List<String> titleList, List<?> dataList, OutputStream outputStream,
			String... dataProperties) {
		exportDataToExcel(null, titleList, dataList, outputStream, dataProperties);
	}

	public static <E> void exportDataToExcel(String tempFilePath, List<String> titleList, List<E> dataList,
			OutputStream outputStream, String... dataProperties) {
		Workbook wb = null;
		try {
			wb = getWorkbookFromTemplate(tempFilePath);
			Sheet sheet = null;
			if(wb.getNumberOfSheets() > 0){
				sheet = wb.getSheetAt(0);
			}else{
				sheet = wb.createSheet();
			}
			int trueRowNum = sheet.getPhysicalNumberOfRows();
			int startRowNum = trueRowNum == 0 ? 0 : (sheet.getLastRowNum() + 1);
			Row row = null;
			Cell cell = null;
			
			if (titleList != null && titleList.size() > 0) {
				CellStyle style = getTitleStyle(wb);
				row = sheet.createRow(startRowNum++);
				for (int i = 0; i < titleList.size(); i++) {
					cell = row.createCell(i);
					cell.setCellStyle(style);
					cell.setCellValue(titleList.get(i));
				}
			}
			int dataColNums = dataProperties.length;
			for (E e : dataList) {
				row = sheet.createRow(startRowNum++);
				for (int i = 0; i < dataColNums; i++) {
					cell = row.createCell(i);
					cell.setCellValue(String.valueOf(ReflectUtils.getValue(e, dataProperties[i])));
				}
			}

			wb.write(outputStream);
		} catch (IOException e1) {
			e1.printStackTrace();
		} finally {
			try {
				if (wb != null) {
					wb.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static CellStyle getTitleStyle(Workbook wb){
		CellStyle style = wb.createCellStyle();
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		
		Font font = wb.createFont();
		font.setBold(true);
		style.setFont(font);
		return style;
	}

	/**
	 * 从模板获取workbook对象
	 * 
	 * @param tempFilePath
	 * @return
	 */
	private static Workbook getWorkbookFromTemplate(String tempFilePath) {
		Workbook wb = null;
		
		try {
			if(tempFilePath != null){
				File file = new File(tempFilePath);
				if (file.exists() && file.isFile()) {
					wb = getWorkbook(new FileInputStream(file), tempFilePath);
				}else{
					wb = new XSSFWorkbook();
				}
			}else{
				wb = new XSSFWorkbook();
			}
			return wb;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		return wb;
	}


	
	/**
	 * 验证该行是否有效（不为空行）
	 * 
	 * @param row
	 * @return
	 */
	private boolean validateRowIsVaild(Row row){
		boolean result = false;
		if(row != null){
			int index = row.getLastCellNum();
			for(int i = 0; i <= index; i++){
				Cell cell = row.getCell(i);
				if(cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK){
					result = true;
				}
			}
		}
		return result;
	}
	public static void test1() {
		OutputStream out = null;
		try {

			String templatePath = "C:/Users/awei/Desktop/南守孝工作/导入模板/线下成绩/导入-线下成绩.xlsx";
			String targetFile = "C:/Users/awei/Desktop/aaaaaaaaaaaaa.xlsx";
			File file = new File(targetFile);
			if (!file.exists()) {
				file.createNewFile();
			}
			out = new FileOutputStream(file);
			List<String> titleList = Arrays.asList("手机号,学段,学科,成绩".split(","));
			String[] titleArray = "phone,sectionName,subjectName,score".split(",");
			List<Map<String, Object>> list = new ArrayList<>();
			for (int i = 0; i < 9; i++) {
				Map<String, Object> map = new HashMap<>();
				map.put("phone", "1347410261" + i);
				map.put("sectionName", "学段" + i);
				map.put("subjectName", "学科" + i);
				map.put("score", 90 + i);
				list.add(map);
			}
			exportDataToExcel(null, titleList, list, out, titleArray);
			out.flush();
			System.out.println("exit");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}
	
	public static void main(String[] args) {
		String path = "C:/Users/awei/Documents/Tencent Files/469432416/FileRecv/ceshi1.xlsx";
		List<Map> entityList = getEntityList(new File(path), Map.class, "code,title,sectionName,subjectName,startDate".split(","));
		System.out.println(entityList);
	}

}
